﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Red5.Frame.Utility.DataBase.MsSql.V1
{
    public class AdoUtility : IAdoUtility
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string ConnectionString
        {
            get
            {
                try
                {
                    //获取配置文件中的连接字符串
                    return System.Configuration.ConfigurationManager.AppSettings["SqlConnString"];
                }
                catch (Exception)
                {
                    throw new Exception("未配置数据库连接");
                }
            }
        }


        #region 执行方法
        /// <summary>
        /// ExecuteDataTable方法：执行SQL查询并返回结果集为一个DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            // 创建一个新的DataTable实例用于存储查询结果
            DataTable dataTable = new DataTable();

            // 使用using语句确保SqlConnection资源的正确释放
            using (var connection = new SqlConnection(ConnectionString))
            {
                // 打开数据库连接
                connection.Open();

                // 创建SqlCommand对象，其中包含要执行的SQL命令
                using (var command = new SqlCommand(sql, connection))
                {
                    // 如果提供了参数，则将它们添加到SqlCommand的Parameters集合中
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    // 使用SqlDataAdapter填充数据表
                    using (var dataAdapter = new SqlDataAdapter(command))
                    {
                        dataAdapter.Fill(dataTable);
                    }
                }
            }
            // 返回填充好的DataTable
            return dataTable;
        }

        /// <summary>
        /// ExecuteNonQuery方法：执行非查询SQL命令（如INSERT、UPDATE或DELETE），并返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            int rowsAffected = 0;

            // 同样使用using语句管理SqlConnection资源
            using (var connection = new SqlConnection(ConnectionString))
            {
                // 打开数据库连接
                connection.Open();

                // 创建SqlCommand对象，并设置SQL命令和连接
                using (var command = new SqlCommand(sql, connection))
                {
                    // 添加参数（如果提供）
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    // 执行非查询操作并获取受影响的行数
                    rowsAffected = command.ExecuteNonQuery();
                }
            }

            // 返回受影响的行数
            return rowsAffected;
        }

        /// <summary>
        /// ExecuteScalar方法：执行SQL查询并返回结果集的第一行第一列的值（通常用于获取单个标量值）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            // 创建SqlConnection实例，并在using语句中确保资源正确释放
            using (var connection = new SqlConnection(ConnectionString))
            {
                // 打开数据库连接
                connection.Open();

                // 创建SqlCommand对象，其中包含要执行的SQL命令
                using (var command = new SqlCommand(sql, connection))
                {
                    // 如果提供了参数，则将它们添加到SqlCommand的Parameters集合中
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    // 执行SQL命令并获取首行首列的值
                    var result = command.ExecuteScalar();

                    // 返回结果，可能为null或对应数据类型的对象
                    return result;
                }
            }
        }

        /// <summary>
        /// 提供一个在事务中执行多条命令的方法
        /// </summary>
        /// <param name="actionList"></param>
        /// <param name="connectionString"></param>
        /// <![CDATA[
        /// 定义在事务中执行的操作
        ///Action<SqlCommand> actions = cmd =>
        ///{
        ///    cmd.CommandText = "INSERT INTO YourTable (...) VALUES (...)";
        ///    cmd.ExecuteNonQuery();
        ///    cmd.CommandText = "UPDATE AnotherTable SET ... WHERE ...";
        ///    cmd.ExecuteNonQuery();
        ///};
        /// ]]>
        public void ExecuteInTransaction(Action<SqlCommand> actionList)
        {

            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // 开启事务
                SqlTransaction transaction = connection.BeginTransaction();

                try
                {
                    // 创建SqlCommand对象并在事务范围内执行操作
                    using (var command = new SqlCommand("", connection, transaction))
                    {
                        foreach (Action<SqlCommand> action in actionList.GetInvocationList())
                        {
                            // 设置当前SqlCommand的CommandText和参数
                            action(command);

                            // 执行单个命令
                            command.ExecuteNonQuery();
                        }

                        // 提交事务
                        transaction.Commit();
                    }
                }
                catch (Exception)
                {
                    // 出现异常时回滚事务
                    transaction.Rollback();
                    throw;
                }
            }
        }
        #endregion

        #region 特殊SQL

        /// <summary>
        /// 获取数据库内所有表信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableInfo()
        {
            DataTable dataTable = ExecuteDataTable("SELECT * FROM sys.tables", new System.Data.SqlClient.SqlParameter[] { });
            return dataTable;
        }
        /// <summary>
        /// 获取数据库内所有表字段信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableColumnInfo()
        {
            DataTable dataTable = ExecuteDataTable("SELECT * FROM INFORMATION_SCHEMA.COLUMNS ORDER BY ORDINAL_POSITION;", new System.Data.SqlClient.SqlParameter[] { });
            return dataTable;
        }
        /// <summary>
        /// 获取数据库内所有前缀为prefixFilter的表字段
        /// </summary>
        /// <param name="prefixFilter"></param>
        /// <returns></returns>
        public DataTable GetTableColumnInfo(string tableName)
        {
            System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new SqlParameter("@tableName", SqlDbType.VarChar, 100) };
            parameters[0].Value = tableName;
            DataTable dataTable = ExecuteDataTable("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName ORDER BY ORDINAL_POSITION;", parameters);
            return dataTable;
        }
        /// <summary>
        /// 获取数据库内大多数表都会定义的主键字段名
        /// </summary>
        /// <returns></returns>
        public string GetPkColumnName()
        {
            object obj = ExecuteScalar(
                "SELECT COLUMN_NAME FROM (SELECT TOP 1 COLUMN_NAME,COUNT(COLUMN_NAME) AS [COUNT] FROM INFORMATION_SCHEMA.COLUMNS GROUP BY COLUMN_NAME ORDER BY COUNT(COLUMN_NAME) desc) AS Z;",
                new SqlParameter[] { });
            return (string)obj;
        }

        /// <summary>
        /// 根据表明获取表索引信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetTableIndex(string tableName)
        {
            System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new SqlParameter("@tableName", SqlDbType.VarChar, 100) };
            parameters[0].Value = tableName;
            DataTable dataTable = ExecuteDataTable("SELECT i.name AS IndexName,i.type_desc AS IndexType,c.name AS ColumnName,ic.key_ordinal AS KeyOrdinalPosition,ic.index_column_id AS ColumnIdInIndex " +
                "FROM sys.indexes AS i " +
                "INNER JOIN  " +
                "sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id " +
                "INNER JOIN " +
                "sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id " +
                "WHERE i.object_id = OBJECT_ID(@tableName);", parameters);
            return dataTable;
        }

        /// <summary>
        /// 根据数据库所有触发器
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetTableTrigger(string tableName)
        {
            System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new SqlParameter("@tableName", SqlDbType.VarChar, 100) };
            parameters[0].Value = tableName;
            DataTable dataTable = ExecuteDataTable("SELECT t.name AS TableName, tr.name AS TriggerName, m.definition AS TriggerDefinition" +
                "FROM  sys.tables t" +
                "JOIN  sys.triggers tr ON t.object_id = tr.parent_id" +
                "JOIN  sys.sql_modules m ON tr.object_id = m.object_id" +
                "WHERE t.is_ms_shipped = 0 AND tr.parent_class = 1 " +
                "ORDER BY t.name, tr.name;", parameters);
            return dataTable;
        }
        #endregion
    }
}
